package com.atguigu.edu.realtime.publisher.mapping;

import com.atguigu.edu.realtime.publisher.bean.AnswerQuesCnt;
import com.atguigu.edu.realtime.publisher.bean.ExamCnt;
import com.atguigu.edu.realtime.publisher.bean.PaperScoreDur;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ExamCountMapper {
    @Select(" select\n" +
            "  paper_name name, sum(user_count) uCnt, \n" +
            "  sum(score_sum)/if(sum(user_count) > 0,sum(user_count),1) avgScore,\n" +
            "  sum(duration_sum)/if(sum(user_count) > 0,sum(user_count),1) avgDur\n" +
            " from\n" +
            " dws_exam_paper_course_test_window\n" +
            " where toYYYYMMDD(stt) = #{date}\n" +
            " group by paper_name")
    List<ExamCnt> selectPaperCnt(Integer date);

    @Select(" select\n" +
            " course_name name, sum(user_count) uCnt, \n" +
            " sum(score_sum)/if(sum(user_count) > 0,sum(user_count),1) avgScore,\n" +
            " sum(duration_sum)/if(sum(user_count) > 0,sum(user_count),1) avgDur\n" +
            " from\n" +
            " dws_exam_paper_course_test_window\n" +
            " where toYYYYMMDD(stt) = #{date}\n" +
            " group by course_name")
    List<ExamCnt> selectCourseCnt(Integer date);

    @Select("select\n" +
            "paper_name,\n" +
            "sum(fail) pool,\n" +
            "sum(good) good,\n" +
            "sum(excellent) excellent\n" +
            "from\n" +
            "dws_exam_paper_grade_test_window\n" +
            "where toYYYYMMDD(stt) = #{date}\n" +
            "group by paper_name")
    List<PaperScoreDur> selectPaperScoreDur(Integer date);

    @Select(" select\n" +
            " question_id,sum(user_count) userCount,sum(exam_count) examCount,\n" +
            " sum(user_correct_count) userCorrectCount,sum(exam_correct_count) examCorrectCount,\n" +
            " avg(exam_correct_count/exam_count) correctPer,avg(user_correct_count/user_count) correctUsrPer\n" +
            " from\n" +
            " dws_exam_question_test_window\n" +
            " where toYYYYMMDD(stt) = #{date}\n" +
            " group by question_id")
    List<AnswerQuesCnt> selectAnswerQuesCnt(Integer date);
}
